#!/bin/bash
# use  xls_ods_ps061614;


# 第一种方法：全量覆盖
# 1.创建第一张表：ods_district 区域字典表

sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--table t_district \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_district_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

# 等待上述语句执行完毕
wait
# 2.创建第一张表：ods_date 时间维度表

sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--table t_date \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_date_ps14 \
--fields-terminated-by '\t' \
--split-by dim_date_id \
--fetch-size 1000 \
--m 2

# 等待上述语句执行完毕
wait
# 如上 使用 Hcatalog 即使我们添加 --delete-target-dir \
# 也是追加  是无效的。



# 第二种方式 增量同步之仅新增同步

# 1. ods_user_login 登录记录表

 sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "select  *,date_format(login_time,'%Y-%m-%d') as dt from xls_source_zdy.t_user_login where 1=1 and \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_user_login_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

# 等待上述语句执行完毕
wait
# 2. ods_goods_evaluation 商品评价表

 sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "select  *,date_format(create_time,'%Y-%m-%d') as dt from xls_source_zdy.t_goods_evaluation where 1=1 and \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_goods_evaluation_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

# 等待上述语句执行完毕
wait
# 3. ods_order_pay 订单组支付表

 sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "select  *,date_format(create_time,'%Y-%m-%d') as dt from xls_source_zdy.t_order_pay where 1=1 and \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_order_pay_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

# 等待上述语句执行完毕
wait
# 3.4 增量同步之新增及更新同步
# 每天新增一个日期分区，同步并存储当天的新增和更新数据

#上述满足条件的表有：

#1. 店铺表 ods_store

# 增量更新之 新增和修改同步
# 数据特点: 既有新数据产生, 也会有历史数据发生修改, 这两种数据都要获取出来
# 6.ods_store_zdy 店铺表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_store WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_store_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 7.ods_trade_area 商圈表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_trade_area WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_trade_area_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 8.ods_location 地址信息表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_location WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_location_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 9.ods_goods 店铺商品表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_goods WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_goods_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 10.ods_goods_class 商品分类表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_goods_class WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_goods_class_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 11.ods_brand 品牌表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_brand WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_brand_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 12.ods_shop_order 订单表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_shop_order WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_shop_order_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 13.ods_shop_order_address_detail 订单详情表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_shop_order_address_detail WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_shop_order_address_detail_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2
wait
# 14.ods_goods_evaluation_detail 商品评价明细表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_goods_evaluation_detail WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_goods_evaluation_detail_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 15.ods_trade_record 交易记录表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_trade_record WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_trade_record_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 16.ods_order_settle 订单结算表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_order_settle WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_order_settle_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 17.ods_refund_order 退款订单表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_refund_order WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_refund_order_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 18.ods_refuse_reason  退款原因表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_refuse_reason WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_refuse_reason_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 19.ods_shop_order_group 订单分组表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_shop_order_group WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_shop_order_group_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 20.ods_shop_order_goods_details 订单和商品的中间表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_shop_order_goods_details WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_shop_order_goods_details_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 21.ods_shop_cart 购物车
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_shop_cart WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_shop_cart_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 22.ods_store_collect 店铺收藏
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_store_collect WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_store_collect_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 23.ods_goods_collect 商品收藏
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_goods_collect WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_goods_collect_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait
# 24.ods_order_delievery_item 订单配送详细信息表
sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect jdbc:mysql://192.168.10.20:3306/xls_source_zdy \
--username root \
--password root \
--query "SELECT *, DATE_FORMAT(coalesce(update_time,create_time), '%Y-%m-%d') AS dt FROM xls_source_zdy.t_order_delievery_item WHERE 1 = 1 AND \$CONDITIONS" \
--hcatalog-database xls_ods_ps061614 \
--hcatalog-table ods_order_delievery_item_ps14 \
--fields-terminated-by '\t' \
--split-by id \
--fetch-size 1000 \
--m 2

wait






